package com.apotik.legenda.main;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;

import com.apotik.legenda.model.Product;
import com.apotik.legenda.model.Supplier;
import com.apotik.legenda.model.User;
import com.apotik.legenda.util.HibernateUtil;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ImportProductExcelToPostgre {

	static ImportProductExcelToPostgre main = null;
	static Session session = null;
	
	public static void main(String[] args) {
		main = new ImportProductExcelToPostgre();
		session = HibernateUtil.getSessionFactory().openSession();
		
		String dirFile = "D:\\Dokumen\\Projects\\LegendaBekasi\\ApotikProgram\\Docs\\excel\\STOCK.xls";
		File file = main.file(dirFile);
		if(file!=null){
			main.insert(file);
		}
		
	}
	
	public File file(String dirFile){
		File file = new File(dirFile);
		System.out.println("File: "+file);
		System.out.println("exists: "+file.exists());
		System.out.println();
		
		if(!file.exists()){
			file = null;
		}
		
		return file;
	}
	
	public void insert(File file){
		try {
			Workbook w = Workbook.getWorkbook(file);
			Sheet sheet = w.getSheet(0);
			
			int mulai=1;
			for(int i=mulai;i<sheet.getRows();i++){
				Cell kodeBarangCell = sheet.getCell(0, i);
				System.out.println("kodeBarang: "+kodeBarangCell.getContents());
				
				Cell namaBarangCell = sheet.getCell(1, i);
				System.out.println("namaBarang: "+namaBarangCell.getContents());
				
				Cell satuanCell = sheet.getCell(8, i);
				System.out.println("satuan: "+satuanCell.getContents());
				
				Cell stokCell = sheet.getCell(21, i);
				System.out.println("stok: "+stokCell.getContents());
				
				Cell hargaBeliCell = sheet.getCell(10, i);
				System.out.println("hargaBeli: "+hargaBeliCell.getContents());
				
				Cell hargaJualCell = sheet.getCell(13, i);
				System.out.println("hargaJual: "+hargaJualCell.getContents());
				
				System.out.println();
				
				Supplier supplier = main.defaultSupplier();
				
				Product item = new Product();
				item.setProductCode(kodeBarangCell.getContents());
				item.setProductName(namaBarangCell.getContents());
				item.setSupplier(supplier);
				item.setExpireDate(main.date(2014, 12, 20));
				item.setProductUnit(satuanCell.getContents());
				item.setAvailableStock(main.parseInt(stokCell.getContents()));
				item.setPriceBuy(main.bigDecimal(hargaBeliCell.getContents()));
				item.setPriceSell(main.bigDecimal(hargaJualCell.getContents()));
				item.setCreatedDate(Calendar.getInstance().getTime());
				item.setCreatedBy(main.user());
				item.setActive(true);
				
				session.beginTransaction();
				session.saveOrUpdate(item);
				session.getTransaction().commit();
				
				System.out.println();
			}
			
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	@SuppressWarnings("unchecked")
	public Supplier defaultSupplier(){
		Supplier s = null;
		
		Criteria cr = session.createCriteria(Supplier.class);
		cr.add(Restrictions.eq("supplierCode", "AAM"));
		List<Supplier> list = cr.list();
		
		if(list!=null){
			if(list.size()>0){
				s = list.get(0);
			}
		}
		
		System.out.println("supplier: "+(s!=null ? s.getSupplierCode():""));
		System.out.println();
		
		return s;
	}
	
	public Date date(int year, int month, int date){
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, year);
		cal.set(Calendar.MONTH, month+1);
		cal.set(Calendar.DAY_OF_MONTH, date);
		
		System.out.println("cal: "+cal);
		System.out.println();
		
		return cal.getTime();
	}
	
	public Integer parseInt(String param){
		Integer result = 0;
		
		try {
			result = Integer.parseInt(param);
		} catch (Exception e) {
			System.out.println("parseInt-exc: "+e.getMessage());
		}
		
		System.out.println("parseInt: "+result);
		System.out.println();
		
		return result;
	}
	
	
	public BigDecimal bigDecimal(String param){
		BigDecimal bg = BigDecimal.ZERO;
		
		try {
			String titikGanti = param.replace(".", ":");
			String[] pisahDec = titikGanti.split(":");
			if(pisahDec.length>0){
				String nilaiUtama = pisahDec[0].replace(",", "");
				bg = new BigDecimal(nilaiUtama);
			}
		} catch (Exception e) {
			System.out.println("bigDecimal-exc: "+e.getMessage());
		}
		
		System.out.println("bigDecimal: "+bg);
		System.out.println();
		
		return bg;
	}
	
	@SuppressWarnings("unchecked")
	public User user(){
		User u = null;
		
		Criteria cr = session.createCriteria(User.class);
		cr.add(Restrictions.eq("userName", "makhsus"));
		List<User> list = cr.list();
		
		if(list!=null){
			if(list.size()>0){
				u = list.get(0);
			}
		}
		
		System.out.println("user: "+u!=null? u.getFullName():"");
		System.out.println();
		
		return u;
	}

}
